-- SQL监控日志表
CREATE TABLE sql_txt_monitor_log (
    id VARCHAR(50) NOT NULL PRIMARY KEY,
    sql_text TEXT NOT NULL,
    sql_params TEXT,
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    execution_duration BIGINT,
    result_count INTEGER,
    status VARCHAR(20) DEFAULT 'SUCCESS',
    error_message TEXT,
    method_name VARCHAR(255),
    class_name VARCHAR(255),
    user_id VARCHAR(50),
    ip_address VARCHAR(50),
    host_name VARCHAR(100),
    query_type VARCHAR(20),
    transaction_id VARCHAR(100),
    call_stack TEXT,
    application_name VARCHAR(100) DEFAULT 'cashbook',
    database_name VARCHAR(100),
    host_ip VARCHAR(50),
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    remark TEXT
);

-- 添加表注释
COMMENT ON TABLE sql_txt_monitor_log IS 'SQL监控日志表，用于记录MyBatis执行的SQL语句和执行情况';

-- 添加字段注释
COMMENT ON COLUMN sql_txt_monitor_log.sql_text IS 'SQL语句文本';
COMMENT ON COLUMN sql_txt_monitor_log.sql_params IS 'SQL参数（JSON格式）';
COMMENT ON COLUMN sql_txt_monitor_log.execution_time IS '执行时间';
COMMENT ON COLUMN sql_txt_monitor_log.execution_duration IS '执行持续时间（毫秒）';
COMMENT ON COLUMN sql_txt_monitor_log.result_count IS '查询结果行数或影响行数';
COMMENT ON COLUMN sql_txt_monitor_log.status IS '执行状态：SUCCESS/FAILED';
COMMENT ON COLUMN sql_txt_monitor_log.error_message IS '错误信息';
COMMENT ON COLUMN sql_txt_monitor_log.method_name IS '执行SQL的方法名';
COMMENT ON COLUMN sql_txt_monitor_log.class_name IS '执行SQL的类名';
COMMENT ON COLUMN sql_txt_monitor_log.user_id IS '执行SQL的用户ID';
COMMENT ON COLUMN sql_txt_monitor_log.ip_address IS '执行SQL的IP地址';
COMMENT ON COLUMN sql_txt_monitor_log.host_name IS '执行SQL的主机名';
COMMENT ON COLUMN sql_txt_monitor_log.query_type IS 'SQL类型：SELECT/INSERT/UPDATE/DELETE';
COMMENT ON COLUMN sql_txt_monitor_log.transaction_id IS '事务ID';
COMMENT ON COLUMN sql_txt_monitor_log.call_stack IS '调用栈信息';
COMMENT ON COLUMN sql_txt_monitor_log.application_name IS '应用名称';
COMMENT ON COLUMN sql_txt_monitor_log.database_name IS '数据库名称';
COMMENT ON COLUMN sql_txt_monitor_log.host_ip IS '数据库服务器IP地址';
COMMENT ON COLUMN sql_txt_monitor_log.create_date IS '创建时间';
COMMENT ON COLUMN sql_txt_monitor_log.update_date IS '更新时间';
COMMENT ON COLUMN sql_txt_monitor_log.create_by IS '创建人';
COMMENT ON COLUMN sql_txt_monitor_log.update_by IS '更新人';
COMMENT ON COLUMN sql_txt_monitor_log.remark IS '备注';

-- 创建索引以提高查询性能
CREATE INDEX idx_sql_monitor_execution_time ON sql_txt_monitor_log(execution_time);
CREATE INDEX idx_sql_monitor_method_name ON sql_txt_monitor_log(method_name);
CREATE INDEX idx_sql_monitor_status ON sql_txt_monitor_log(status);
CREATE INDEX idx_sql_monitor_query_type ON sql_txt_monitor_log(query_type);
CREATE INDEX idx_sql_monitor_user_id ON sql_txt_monitor_log(user_id);
CREATE INDEX idx_sql_monitor_duration ON sql_txt_monitor_log(execution_duration);

-- 创建复合索引
CREATE INDEX idx_sql_monitor_time_status ON sql_txt_monitor_log(execution_time, status);
CREATE INDEX idx_sql_monitor_method_time ON sql_txt_monitor_log(method_name, execution_time);

-- 创建触发器来自动更新 update_date 字段
CREATE OR REPLACE FUNCTION update_updated_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_date = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_sql_txt_monitor_log_updated_date 
    BEFORE UPDATE ON sql_txt_monitor_log 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_column();
